import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBMail
{

	private DB db;

	private Properties config = new Properties();

	public DBMail()
	{
		try
		{
			config.load(new FileReader("dbmail.properties"));
		}
		catch(FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch(IOException e)
		{
			e.printStackTrace();
		}
	}

	private void cercaSpirati()
	{
		PreparedStatement getMboxes = db.prepare(config
				.getProperty("queries.mailboxes.temporaries"));
		PreparedStatement getRecords = db.prepare(config
				.getProperty("queries.expired"));
		PreparedStatement delRecords = db
				.prepare("delete from dbmail_physmessage where id=?");
		ResultSet mb, rs = null;
		int id = 0;
		String date;
		int tot, sTot = 0;
		String mboxName = "";
		try
		{
			sTot = 0;
			mb = getMboxes.executeQuery();
			while(mb.next())
			{
				tot = 0;
				mboxName = mb.getString(1);
				// mboxId = mb.getInt(2);
				getRecords.setString(1, mboxName);
				// System.out.println(getExpired.toString());

				rs = getRecords.executeQuery();
				while(rs.next())
				{
					id = Integer.parseInt(rs.getString(1));
					date = rs.getString(2);
					// System.out.println(id + ", " + mboxName + ", " + dups);
					if(date != "")
					{
						delRecords.setInt(1, id);
						// System.out.println(delDups.toString());
						// delDups.executeUpdate();
						tot++;
					}
				}
				sTot += tot;
				if(tot != 0)
				{
					System.out.println(mboxName + ": rimossi " + tot + " duplicati");
				}
			}
			System.out.println("Rimossi " + sTot + " duplicati complessivi");
		}
		catch(SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void cercaDuplicati()
	{
		PreparedStatement getMboxes = db.prepare("queries.mailboxes.all");
		PreparedStatement getDups = db.prepare(config
				.getProperty("queries.duplicates"));
		PreparedStatement delDups = db
				.prepare("delete from dbmail_physmessage where id=?");
		ResultSet mb, rs = null;
		int id = 0;
		int dups = 0;
		int tot, sTot = 0;
		String mboxName = "";
		int mboxId = 0;
		try
		{
			do
			{
				sTot = 0;
				mb = getMboxes.executeQuery();
				while(mb.next())
				{
					tot = 0;
					mboxName = mb.getString(1);
					mboxId = mb.getInt(2);
					getDups.setInt(1, mboxId);
					rs = getDups.executeQuery();
					while(rs.next())
					{
						id = Integer.parseInt(rs.getString(1));
						dups = Integer.parseInt(rs.getString(2));
						// System.out.println(id + ", " + mboxName + ", " + dups);
						if(dups > 1)
						{
							delDups.setInt(1, id);
							// System.out.println(delDups.toString());
							delDups.executeUpdate();
							tot++;
						}
					}
					sTot += tot;
					if(tot != 0)
					{
						System.out.println(mboxName + ": rimossi " + tot + " duplicati");
					}
				}
				System.out.println("Rimossi " + sTot + " duplicati complessivi");
			}
			while(sTot != 0);
		}
		catch(SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * L'argomento è la directory contenente le regioni.
	 */
	public static void main(String[] args)
	{
		DBMail c = new DBMail();
		c.db = new DB(DB.url, "dbmail", args[0]);
		// System.out.println(c.config.getProperty("queries.expired"));
		// c.cercaDuplicati();
		c.cercaSpirati();
		c.db.free();
	}
}
